const Router = require('koa-router');
const tool = require("../../tools/tool");
const router = new Router();
const {db, QueryTypes} = require('../../models/db')
// 采购汇总-商品
router.post('/pur/listpur', async ctx => {
    let user = ctx.user
    let body = ctx.request.body
    let formData = body.formData
    let tenantId = user.tenantId;
    let beginDate = formData.beginDate ? formData.beginDate: '2001-01-01';
    let endDate = formData.endDate?formData.endDate:tool.getDay();
    let buId = formData.buId?formData.buId:0;
    let goodCatCode = await tool.getGoodCode(ctx)
    let remark = formData.remark?tool.xe.trim(formData.remark):'';
    let invName = formData.invName?formData.invName:'';
    let billState = formData.billState?formData.billState:'';
    let currentPage = body.currentPage?body.currentPage:1;
    let pageSize = body.pageSize?body.pageSize:200;
    let offset = (currentPage-1) * pageSize;

    let excel = body.excel
    let sqls = `select distinct a.id, a.tenantId, billDate, a.billNo, billState, billType, a.transType, billStateName,printNo,a.cbyName,a.createdAt,
       transTypeName, buId, contactCatId, buName, totalAmount, disAmount, ysAmount, rpAmount, accId, accAmount, billStateSort,
       arrears,hxAt,
       accId1, accAmount1, nowCheck, yhAmount, (nowCheck + yhAmount) hxAmount,
       totalAmount-disAmount-rpAmount-ysAmount-nowCheck-yhAmount leftAmount,
       a.remark 
        from v_main_withhx a inner join v_main_info b on a.tenantId=b.tenantId and a.id=b.iid 
       where a.tenantId=${tenantId} and a.transType=150501 
     `

    if(billState !== 'all') {
        sqls += ` and a.billState='${billState}' `
    }
    else {
        sqls += ` and a.billState<>'del' `
    }
    if(beginDate) {
        sqls += ` and a.billDate>='${beginDate}' `
    }
    if(beginDate) {
        sqls += ` and a.billDate<='${endDate}' `
    }
    if(buId) {
        sqls += ` and a.buId=${buId} `
    }
    if(invName) {
        sqls += ` and b.invName like '%${invName}%' `
    }
    if(remark) {
        sqls += ` and a.remark like '%${remark}%' `
    }
    if(goodCatCode) {
        sqls += ` and b.goodCatCode like '${goodCatCode}%' `
    }
    if(excel) {
        sqls += ` order by billStateSort, billDate desc `
        let list = await db.query(sqls, {type: QueryTypes.SELECT})

        return tool.doExcelOut(ctx, list)
    }
    else {
        let sort = tool.getSort(ctx)
        let sorts = ' billStateSort, billDate desc '
        if(sort) sorts = sort
        let totalModel = await db.query(`select count(*) total from (${sqls}) a `, {type: QueryTypes.SELECT, plain: true})
        sqls = ` select * from (${sqls}) a order by ${sorts} offset ${offset} rows fetch next ${pageSize} rows only;`
        let list = await db.query(sqls, {type: QueryTypes.SELECT})
        return ctx.suc('', {
            total: totalModel?totalModel.total: 0,
            list: list
        })
    }
})





module.exports = router;